import os
import sys
import io
import pandas as pd
pd.set_option('display.unicode.east_asian_width', True)
import json
from datetime import datetime
import openpyxl
from openpyxl.styles import Font, Color,PatternFill,Alignment
from openpyxl.utils import get_column_letter
import platform
if platform.system().lower()=='windows':
    try:
        import win32com.client
        HAS_WIN32COM = True
    except ImportError:
        HAS_WIN32COM = False
else:
    HAS_WIN32COM = False
import msoffcrypto
import warnings
warnings.filterwarnings("ignore", category=UserWarning, module='openpyxl')

class orders:
    def __init__(self,config_dir=r'D:\NutsStore\ktt',save_temp_root_dir=r'E:\temp\ejj\团购群'):
        self.config_dir=config_dir
        self.save_temp_root_dir=save_temp_root_dir
        with open(os.path.join(self.config_dir,'order_col_map_new.config'),'r',encoding='utf-8') as f:
            self.map_dic=json.load(f)

        with open(os.path.join(self.config_dir,'html_goods_display.config'),'r',encoding='utf-8') as f:
            self.html_goods_display=json.load(f)

        with open(os.path.join(os.path.dirname(__file__),'configs','ktt.config'),'r',encoding='utf-8') as f:
            self.ktt_config=json.load(f)

        self.spec_code_config=pd.read_excel(os.path.join(self.config_dir,'三恒商品规格编码.xlsx'))
        self.ice_templates = ['荔枝小哥','荔枝','桂味荔枝','鸡嘴荔枝']
        
    def _extract_file_number(self, filename):
        import re
        numbers = re.sub(r'\D', '', os.path.basename(filename))
        return numbers[:8].zfill(8) if numbers else datetime.now().strftime("%Y%m%d")

    def ice_bag(self, df, addr_col=None, spec=None, output_path=None, use_weight=False):
        if use_weight:
            if not spec:
                raise ValueError("按重量判断需要提供规格列名(spec)")
            ice_series = df.apply(lambda row: 1 if '5斤' in str(row[spec]) else (2 if '10斤' in str(row[spec]) else 0), axis=1)
        else:
            if not addr_col:
                raise ValueError("按地区判断需要提供地址列名(addr_col)")
            ice_series = df[addr_col].str.startswith(('广西','广东')).map({True:1, False:2})

        total = int(ice_series.sum())
        df['冰袋数量'] = ice_series
        
        if output_path:
            df.to_excel(output_path, index=False)
        
        return total,ice_series

    def exp_html_config(self):
        return self.html_goods_display
        
    def read_order(self, order_fn_input, sheet_name='商品列表'):
        if not os.path.exists(order_fn_input):
            raise FileNotFoundError(f"文件不存在: {order_fn_input}")

        max_retries = 3
        retry_count = 0
        
        while retry_count < max_retries:
            try:
                try:
                    with open(order_fn_input, 'rb') as f:
                        office_file = msoffcrypto.OfficeFile(f)
                        office_file.load_key(password='7788')
                        decrypted = io.BytesIO()
                        office_file.decrypt(decrypted)
                        
                        df = pd.read_excel(
                            decrypted,
                            sheet_name=sheet_name,
                            engine='openpyxl'
                        )
                        return df
                except Exception as e:
                    if 'Unencrypted document' in str(e):
                        df = pd.read_excel(
                            order_fn_input,
                            sheet_name=sheet_name,
                            engine='openpyxl'
                        )
                        return df
                    elif HAS_WIN32COM:
                        xlsApp = win32com.client.DispatchEx('Excel.Application')
                        xlsApp.EnableEvents = False
                        xlsApp.DisplayAlerts = False
                        xlwb = xlsApp.Workbooks.Open(
                            order_fn_input,
                            UpdateLinks=False,
                            ReadOnly=False,
                            Format=None,
                            Password=7788,
                            WriteResPassword=''
                        )
                        xlwb.SaveAs(order_fn_input, None, "", "")
                        xlwb.Close(False)
                        xlsApp.Quit()
                    else:
                        raise

                df = pd.read_excel(
                    order_fn_input,
                    sheet_name=sheet_name,
                    engine='openpyxl'
                )
                return df

            except Exception as e:
                retry_count += 1
                if retry_count >= max_retries:
                    raise Exception(f"读取Excel文件失败(重试{max_retries}次): {str(e)}")
                print(f"读取失败(第{retry_count}次重试): {str(e)}")
                import time
                time.sleep(1)

    def _process_template_data(self, group, template):
        if template not in self.map_dic:
            raise ValueError(f"模板'{template}'在order_col_map_new.config中未定义")
            
        config = self.map_dic[template]
        spec_names = group['商品编码'].map(
            self.spec_code_config.set_index('商品编码')['打印规格名称']
        )
        new_df = pd.DataFrame(columns=config['col_name'])

        for col in config['col_name']:
            if isinstance(config['fix_col'], dict) and col in config['fix_col']:
                new_df[col] = config['fix_col'][col]
                continue
                
            if col in config['map']:
                src_col = config['map'][col]
                if src_col:
                    if src_col == '打印规格名称':
                        new_df[col] = spec_names
                    elif src_col not in group.columns:
                        raise ValueError(f"映射配置错误: 模板'{template}'中列'{col}'映射到不存在的源列'{src_col}'")
                    else:
                        new_df[col] = group[src_col]
        
        return new_df, config

    def exp_order(self, df, seq_no="01", expand_qty='yes', return_stream=False, folder_date=None):
        if expand_qty == 'yes':
            expanded_rows = []
            for _, row in df.iterrows():
                qty = int(row['数量'])
                if qty > 1:
                    for _ in range(qty):
                        new_row = row.copy()
                        new_row['数量'] = 1
                        expanded_rows.append(new_row)
                else:
                    expanded_rows.append(row.copy())
            df = pd.DataFrame(expanded_rows).reset_index(drop=True)
            
        date_str = folder_date if folder_date else datetime.now().strftime("%Y%m%d")
        output_dir = os.path.join(self.save_temp_root_dir,"导出订单", date_str)
        os.makedirs(output_dir, exist_ok=True)

        template_names = df['商品编码'].map(
            self.spec_code_config.set_index('商品编码')['模板名称']
        )
        
        if template_names.isnull().any():
            missing_codes = df[template_names.isnull()]['商品编码'].unique()
            raise ValueError(f"以下商品编码在规格编码表中找不到对应模板: {missing_codes}")
            
        all_results = {}
        for template, group in df.groupby(template_names):
            if template not in self.map_dic:
                raise ValueError(f"模板'{template}'在order_col_map_new.config中未定义")
                
            config = self.map_dic[template]
            
            spec_names = group['商品编码'].map(
                self.spec_code_config.set_index('商品编码')['打印规格名称']
            )
            new_df = pd.DataFrame(columns=config['col_name'])
           
            for col in config['col_name']:  
                if col in config['map']:
                    src_col = config['map'][col]
                    if src_col:
                        if src_col == '打印规格名称':
                            new_df[col] = spec_names
                        elif src_col not in group.columns:
                            raise ValueError(f"映射配置错误: 模板'{template}'中列'{col}'映射到不存在的源列'{src_col}'")
                        else:
                            new_df[col] = group[src_col]
            
            if isinstance(config['fix_col'], dict):
                for col, val in config['fix_col'].items():
                    new_df[col] = val
            
            spec_col_name = None
            qty_col_name = None
            for col in config['col_name']:
                if config['map'].get(col) == '打印规格名称':
                    spec_col_name = col
                elif config['map'].get(col) == '数量':
                    qty_col_name = col

            if not (spec_col_name and qty_col_name):
                raise ValueError("配置文件中缺少'打印规格名称'或'数量'的映射")
            if spec_col_name not in new_df.columns or qty_col_name not in new_df.columns:
                raise ValueError(f"DataFrame中缺少'{spec_col_name}'或'{qty_col_name}'列")

            new_df[qty_col_name] = pd.to_numeric(new_df[qty_col_name], errors='coerce').fillna(0)
            
            stats = new_df.groupby(spec_col_name)[qty_col_name].sum().to_dict()
            total = new_df[qty_col_name].sum()
            
            fn_post = f"共{int(total)}件 " + " & ".join([f"{spec} {int(count)}件" for spec, count in stats.items()])
            
            output_name = f"{date_str}-{seq_no}-{template}-{fn_post}-导出订单.xlsx"
            output_path = os.path.join(output_dir, output_name)

            if template in self.ice_templates:
                address_col = next((col for col in config['col_name'] 
                                 if config['map'].get(col) == '详细地址'), None)
                spec_col = next((col for col in config['col_name']
                               if config['map'].get(col) == '打印规格名称'), None)
                
                if address_col and address_col in new_df.columns:
                    ice_count=self.ice_bag(new_df, address_col, spec_col, output_path, use_weight=True)
                    new_df['冰袋数量'] = ice_count[1]
            
            file_stream = None
            if return_stream:
                file_stream = io.BytesIO()
                new_df.to_excel(file_stream, index=False)
                file_stream.seek(0)
            else:
                for fn in os.listdir(output_dir):
                    if fn.split('.')[-1]=='xlsx' and fn.split('.')[0].split('-')[0]==date_str and fn.split('.')[0].split('-')[2]==template and fn.split('.')[0].split('-')[1].zfill(2)==seq_no:
                        os.remove(os.path.join(output_dir,fn))
                new_df.to_excel(output_path, index=False)

            result = {
                'file_stream': file_stream,
                'df': new_df,
                'total_txt': f"合计：{int(total)}件\n其中：\n" + "\n".join([f"{spec}：{int(count)}件" for spec, count in stats.items()]),
                'fn_post': f"共{int(total)}件 " + " & ".join([f"{spec} {int(count)}件" for spec, count in stats.items()]),
                'desc_col': config['sum_col_num']+1,
                'desc_row': len(new_df) + 3,
                'desc_yn': config.get('summary_total_show_yn', 'yes'),
                'output_path': output_path
            }

            all_results[template] = result
        
        return all_results

    def generate_order_to_supplier(self,order_fn_input,seq_no="01",sheet_name='商品列表',expand_qty='yes',df=None):
        if order_fn_input is None and df is None:
            raise ValueError("必须提供 order_fn_input这个xlsx文件 或 df")
    
        if df is None:
            df = self.read_order(order_fn_input=order_fn_input, sheet_name=sheet_name)

        file_number = self._extract_file_number(os.path.basename(order_fn_input))
        results=self.exp_order(df=df,seq_no=seq_no,expand_qty=expand_qty,folder_date=file_number)
        
        output_paths = []
        for template, result in results.items():
            self.xlsx_format(
                xlsx=result['output_path'],
                desc=result['total_txt'],
                desc_row=result['desc_row'],
                desc_col=result['desc_col'],
                desc_yn=result['desc_yn']
            )
            output_paths.append(result['output_path'])
            
        return output_paths[0] if len(output_paths) == 1 else output_paths
    
    def xlsx_format(self,xlsx,desc,desc_row,desc_col,desc_yn='yes'):
        wb=openpyxl.load_workbook(xlsx)
        ws=wb.active

        font=Font(size=13,bold=True)
        ws['A1'].font=font

        if desc_yn=='yes':
            ws.cell(desc_row,desc_col).value=desc
            font=Font(bold=True)
            ws.cell(desc_row,desc_col).font=font
            yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
            ws.cell(desc_row,desc_col).fill=yellow_fill
            ws.cell(desc_row,desc_col).alignment = Alignment(wrapText=True)

        for cls in ['A','B','C','D','F','G','H','I']:
            cell=ws[cls+'1']
            font=Font(bold=True)
            cell.font=font

        col_letter = get_column_letter(desc_col)
        ws.column_dimensions[col_letter].width = 40
        
        wb.save(xlsx)

class MyCustomError(Exception):
    def __init__(self, message):
        super().__init__(message)
        self.message = message

if __name__=='__main__':
    p=orders(config_dir=r'D:\工作目录\ktt')
    out_fn=p.generate_order_to_supplier(
        order_fn_input=r'E:\temp\ejj\团购群\订单\20250703-综合-02.xlsx',
        seq_no='01',
        sheet_name='商品列表',
        expand_qty='yes'
    )
    print(out_fn)
